﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


namespace ERP.Data
{
    public class Sales
    {
        public Sales()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        sERPEntities data = new sERPEntities();
        Common commonclass = new Common();

        #region hoadon - chi tiet hoa don

        public bool AddHoadon(Hoadon entity)
        {
            data.AddToHoadons(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateHoadon(Hoadon entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelHoadon(Hoadon entity)
        {
            data.DeleteObject(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public DataTable loadDSHoadonAll()
        {

            var result = from a in data.Hoadons
                         select new
                                {
                                    a.IdHoadon,
                                    a.MaHoadon,
                                    a.Ngay,
                                    a.Sotien,
                                    a.Sotiengiamgia,
                                    a.Giamgia,
                                    a.Vat,
                                    a.SotienVat,
                                    a.Tongtien,
                                    a.IdKhachhang,
                                    a.IdKho,
                                    a.IdNguoilap
                                };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "Hoadon";
            return dt;
        }

        public DataTable loadDSHoadonByDate(DateTime ngaybd, DateTime ngaykt)
        {

            var result = from a in data.Hoadons
                         where a.Ngay >= ngaybd && a.Ngay <= ngaykt
                         select new
                         {
                             a.IdHoadon,
                             a.MaHoadon,
                             a.Ngay,
                             a.Sotien,
                             a.Sotiengiamgia,
                             a.Giamgia,
                             a.Vat,
                             a.SotienVat,
                             a.Tongtien,
                             a.IdKhachhang,
                             a.IdKho,
                             a.IdNguoilap
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSHoadonByDate";
            return dt;
        }

        public DataTable LoadChitietHdByIdHD(long idHoadon)
        {

            var result = from a in data.Hoadons
                         join b in data.Chitiet_Hoadon on a.IdHoadon equals b.IdHoadon
                         where a.IdHoadon == idHoadon
                         orderby a.Ngay descending
                         select new
                                {
                                    b.IdHoadon,
                                    b.IdDonvitinh,
                                    b.IdChitiet,
                                    b.Dongia,
                                    b.Soluong,
                                    b.IdHanghoa
                                };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "LoadChitietHdByIdHD";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            return dt;
        }

        public DataTable LoadChitietHdByMaHD(string maHoadon)
        {

            var result = from a in data.Hoadons
                         join b in data.Chitiet_Hoadon on a.IdHoadon equals b.IdHoadon
                         where a.MaHoadon == maHoadon
                         orderby a.Ngay descending
                         select new
                         {
                             a.Vat,
                             a.SotienVat,
                             a.Giamgia,
                             a.Sotiengiamgia,
                             a.Sotien,
                             a.MaHoadon,
                             a.Tongtien,
                             b.IdHoadon,
                             b.IdDonvitinh,
                             b.IdChitiet,
                             b.Dongia,
                             b.Soluong,
                             Soluongtra = b.Soluong,
                             b.IdHanghoa
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "LoadChitietHdByIdHD";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                dt.Columns.Add("Stt", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                    dt.Rows[i]["Stt"] = i;
                }
            }
            return dt;
        }
        
        public DataTable LoadChitietHdByIDHD(long idHoadon)
        {

            var result = from a in data.Hoadons
                         join b in data.Chitiet_Hoadon on a.IdHoadon equals b.IdHoadon
                         join h in data.Dm_Hanghoa on b.IdHanghoa equals h.IdHanghoa
                         join d in data.Dm_Donvitinh on b.IdDonvitinh equals d.IdDonvitinh
                         where a.IdHoadon == idHoadon
                         orderby a.Ngay descending
                         select new
                         {
                             a.Vat,
                             a.SotienVat,
                             a.Giamgia,
                             a.Sotiengiamgia,
                             a.Sotien,
                             a.MaHoadon,
                             a.Tongtien,
                             b.IdHoadon,
                             b.IdDonvitinh,
                             b.IdChitiet,
                             b.Dongia,
                             b.Soluong,
                             b.IdHanghoa,
                             h.TenHanghoa,
                             d.TenDonvitinh

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "LoadChitietHdByIDHD";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            return dt;
        }
        
        public bool AddChitietHoadon(Chitiet_Hoadon entity)
        {
            data.AddToChitiet_Hoadon(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateChitietHoadon(Chitiet_Hoadon entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitietHoadon(Chitiet_Hoadon entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }
        
        #endregion

        #region phieu nhập - chi tiet phieu nhap

        public bool AddPhieunhap(Phieunhap entity)
        {
            data.AddToPhieunhaps(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdatePhieunhap(Phieunhap entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelPhieunhap(Phieunhap entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool AddChitietPhieunhap(Chitiet_Phieunhap entity)
        {
            data.AddToChitiet_Phieunhap(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateChitietPhieunhap(Chitiet_Phieunhap entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitietPhieunhap(Chitiet_Phieunhap entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitietPhaphangAllbyID(long idPhieunhap)
        {

            var result = from a in data.Chitiet_Phieunhap
                         where a.IdPhieunhap == idPhieunhap
                         select a;

            foreach (var entity in result.ToList())
            {
                data.DeleteObject(entity); 
                data.SaveChanges();
                
            }
            return true;
        }

        public DataTable loadDSPhieunhapAll()
        {
            var result = from a in data.Phieunhaps
                         select new
                         {
                             a.IdPhieunhap,
                             a.MaPhieunhap,
                             a.Ngay,
                             a.Sotien,
                             a.Vat,
                             a.Tongtien,
                             a.IdNhacungcap,
                             a.IdKho,
                             a.IdNguoilap,
                             a.Ghichu,
                             a.NgayNhap
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSPhieunhapAll";
            return dt;
        }

        public DataTable loadDSPhieunhapByDate(DateTime ngaybd, DateTime ngaykt)
        {

            var result = from a in data.Phieunhaps
                         where a.Ngay >= ngaybd && a.Ngay < ngaykt
                         select new
                         {
                             a.IdPhieunhap,
                             a.MaPhieunhap,
                             a.Ngay,
                             a.Sotien,
                             a.Vat,
                             a.Tongtien,
                             a.IdNhacungcap,
                             a.IdKho,
                             a.IdNguoilap,
                             a.Ghichu,
                             a.NgayNhap
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSPhieunhapByDate";
            return dt;
        }

        public DataTable LoadChitietPhieunhapByIdPN(long idPhieunhap)
        {

            var result = from a in data.Phieunhaps
                         join b in data.Chitiet_Phieunhap on a.IdPhieunhap equals b.IdPhieunhap
                         where a.IdPhieunhap == idPhieunhap
                         orderby a.Ngay descending
                         select new
                         {
                             b.IdPhieunhap,
                             b.IdDonvitinh,
                             b.IdChitiet,
                             b.Dongia,
                             b.Soluong,
                             b.IdHanghoa
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "LoadChitietPhieunhapByIdPN";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            else
            {
                dt.Columns.Add("IdPhieunhap", typeof(long));
                dt.Columns.Add("IdDonvitinh", typeof(long));
                dt.Columns.Add("IdChitiet", typeof(long));
                dt.Columns.Add("Dongia", typeof(decimal));
                dt.Columns.Add("Soluong", typeof(long));
                dt.Columns.Add("IdHanghoa", typeof(long));
                dt.Columns.Add("Thanhtien", typeof(decimal));
            }
            return dt;
        }

        #endregion

        #region phieu thu
        //them 
        public bool AddPhieuthu(Phieuthu entity)
        {
            data.AddToPhieuthus(entity);
            return data.SaveChanges() > 0 ? true : false;
        }
        // sua
        public bool UpdatePhieuthu(Phieuthu entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }
        // xoa
        public bool DeletePhieuthu(Phieuthu entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        // lay theo ID
        public Phieuthu GetPhieuthuById(long idPhieuthu)
        {
            var result = from a in data.Phieuthus
                         where a.IdPhieuthu == idPhieuthu
                         select a;

            if (result != null)
            {
                return result.Single();
            }
            return null;
        }

        // lay tat ca  
        public List<Phieuthu> GetListPhieuthuAll()
        {

            var result = from a in data.Phieuthus
                         select a;
            return result.ToList();

        }

        public DataTable loadDSPhieuthuAll()
        {
            var result = from a in data.Phieuthus
                         where a.IdNoKH == 0
                         select new
                         {
                             a.IdPhieuthu,
                             a.IdHoadon,
                             a.IdKhachhang,
                             a.IdKho,
                             a.IdTiente,
                             a.Lydo,
                             a.MaPhieuthu,
                             a.Ngaythu,
                             a.Nguoinop,
                             a.Sotien,
                             a.SotienQuydoi,
                             a.Tygia,
                             a.IdNguoilap,
                             a.Donvi,
                             a.Diachi
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "Phieuthu";
            return dt;
        }

        // search phieu thu

        public List<Phieuthu> GetPhieuthuByDate(DateTime ngaybatdau, DateTime ngayketthuc)
        {

            var result = from a in data.Phieuthus
                         where a.Ngaythu >= ngaybatdau && a.Ngaythu <= ngayketthuc
                         select a;
            return result.ToList();
        }
        //


        #endregion

        #region phieu Chi

        //them 
        public bool AddPhieuchi(Phieuchi entity)
        {
            data.AddToPhieuchis(entity);
            return data.SaveChanges() > 0 ? true : false;
        }
        // sua
        public bool UpdatePhieuchi(Phieuchi entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }
        // xoa
        public bool DeletePhieuchi(Phieuchi entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        // lay theo ID
        public Phieuchi GetPhieuchiById(long idPhieuchi)
        {
            var result = from a in data.Phieuchis
                         where a.IdPhieuchi == idPhieuchi
                         select a;

            if (result != null)
            {
                return result.Single();
            }
            return null;
        }

        // lay theo IdTrahang
        public Phieuchi GetPhieuchiByIdTrahang(long idTrahang)
        {

            var result = from a in data.Phieuchis
                         where a.IdTrahang == idTrahang
                         select a;
            return result.ToList().Count > 0 ? result.FirstOrDefault() : new Phieuchi();
        }

        // xoa phieu chi theo idtrahang
        public void DeletePhieuchiByIdTrahang(long idTrahang)
        {

            var result = from a in data.Phieuchis
                         where a.IdTrahang == idTrahang
                         orderby a.Ngaychi descending
                         select a;

            if (result.ToList().Count > 0)
            {
                data.DeleteObject(result.FirstOrDefault());
                data.SaveChanges();
            }

        }

        // lay tat ca  
        public List<Phieuchi> GetListPhieuchiAll()
        {

            var result = from a in data.Phieuchis
                         select a;
            return result.ToList();

        }

        public DataTable loadDSPhieuchiAll()
        {

            var result = from a in data.Phieuchis
                         select new
                         {
                             a.IdPhieuchi,
                             a.IdPhieunhap,
                             a.IdNhacungcap,
                             a.IdKho,
                             a.IdTiente,
                             a.Lydo,
                             a.MaPhieuchi,
                             a.Ngaychi,
                             a.Nguoinhan,
                             a.Sotien,
                             a.SotienQuydoi,
                             a.Tygia,
                             a.IdNguoilap,
                             a.Donvi,
                             a.Diachi
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "Phieuchi";
            return dt;
        }

        // lay tat ca  
        public List<Phieuchi> GetlistPhieuchiAll()
        {

            var result = from a in data.Phieuchis
                         select a;
            return result.ToList();

        }

        public List<Phieuchi> SearchExpenseByDate(DateTime ngaybatdau, DateTime ngayketthuc)
        {

            var result = from a in data.Phieuchis
                         where a.Ngaychi >= ngaybatdau && a.Ngaychi <= ngayketthuc
                         select a;
            return result.ToList();
        }
        //

        // xoa phieu chi theo idPhieunhap
        public void DeletePhieuchiByIdPhieunhap(long idPhieunhap)
        {

            var result = from a in data.Phieuchis
                         where a.IdPhieunhap == idPhieunhap
                         select a;

            foreach (var entity in result.ToList())
            {
                data.DeleteObject(entity);
                data.SaveChanges();
            }
        }

        #endregion

        #region Ghi no ban hang ( khach hang)

        public bool AddNoKh(NoKH entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateNoKh(NoKH entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelNoKh(NoKH entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public DataTable loadDSNokhAll()
        {

            var result = from a in data.NoKHs
                         select new
                         {
                             a.IdHoadon,
                             a.IdKhachhang,
                             a.Ngay,
                             a.Psno,
                             a.SotienHoadon
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "NoKH";
            return dt;
        }

        //public DataTable loadDSNokhByIdKh(long idKhachhang)
        //{
        //    //return _Sales.GetCongnoKh(idKhachhang);
            
        //}

        //public DataTable LoadCongNoKHAll()
        //{
        //    return _Sales.GetCongnoKhall();
        //    //return RetrievalProcedures.GetCongnoKhall();
        //}

        public DataTable GetListPhieuthuNoKhByIdKH(long idKhachhang)
        {

            var result = from a in data.Phieuthus
                         where a.IdNoKH > 0 && a.IdKhachhang == idKhachhang
                         select new
                         {
                             a.IdPhieuthu,
                             a.IdHoadon,
                             a.IdKhachhang,
                             a.IdKho,
                             a.IdTiente,
                             a.Lydo,
                             a.MaPhieuthu,
                             a.Ngaythu,
                             a.Nguoinop,
                             a.Sotien,
                             a.SotienQuydoi,
                             a.Tygia,
                             a.IdNguoilap,
                             a.Donvi,
                             a.Diachi,
                             a.IdNoKh
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "Phieuthu";
            return dt;
        }

        public DataTable GetListPhieuthuNoKhAll()
        {
            var result = from a in data.Phieuthus
                         where a.IdNoKH > 0
                         select new
                         {
                             a.IdPhieuthu,
                             a.IdHoadon,
                             a.IdKhachhang,
                             a.IdKho,
                             a.IdTiente,
                             a.Lydo,
                             a.MaPhieuthu,
                             a.Ngaythu,
                             a.Nguoinop,
                             a.Sotien,
                             a.SotienQuydoi,
                             a.Tygia,
                             a.IdNguoilap,
                             a.Donvi,
                             a.Diachi,
                             a.IdNoKh
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "Phieuthu";
            return dt;
        }

        public DataTable LoadChitietNoByIdKH(long idKhachhang)
        {

            var result = from a in data.NoKHs
                         join b in data.Hoadons on a.IdHoadon equals b.IdHoadon
                         where a.IdKhachhang == idKhachhang
                         orderby a.Ngay descending
                         select new
                         {
                             a.IdHoadon,
                             b.MaHoadon,
                             a.IdKhachhang,
                             a.Ngay,
                             a.Psno,
                             a.SotienHoadon
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "LoadChitietNoByIdKH";
            return dt;
        }


        #endregion

        #region Ghi no mua hang ( nha cung cap)

        public bool AddNoNcc(NoNCC entity)
        {
            data.AddToNoNCCs(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateNoNcc(NoNCC entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelNoNcc(NoNCC entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public DataTable loadDSNoNccAll()
        {

            var result = from a in data.NoNCCs
                         select new
                         {
                             a.IdPhieunhap,
                             a.IdNhacungcap,
                             a.Ngay,
                             a.Psno,
                             a.SotienHoadon
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "NoNCC";
            return dt;
        }

        //public DataTable loadDSNoNccByIdKh(long idNhacungcap)
        //{
        //    return _Sales.GetCongnoNcc(idNhacungcap);
        //    //return RetrievalProcedures.GetCongnoNcc(idNhacungcap);
        //}

        //public DataTable LoadCongNoNCCAll()
        //{
        //    return _Sales.GetCongnoNccall();
        //    //return RetrievalProcedures.GetCongnoNccall();
        //}

        public DataTable GetListPhieuchiNoNccByIdNcc(long idNhacungcap)
        {

            var result = from a in data.Phieuchis
                         where a.IdNoNCC > 0 && a.IdNhacungcap == idNhacungcap
                         select new
                         {
                             a.IdPhieuchi,
                             a.IdPhieunhap,
                             a.IdNhacungcap,
                             a.IdKho,
                             a.IdTiente,
                             a.Lydo,
                             a.MaPhieuchi,
                             a.Ngaychi,
                             a.Nguoinhan,
                             a.Sotien,
                             a.SotienQuydoi,
                             a.Tygia,
                             a.IdNguoilap,
                             a.Donvi,
                             a.Diachi,
                             a.IdNoNcc
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "GetListPhieuchiNoNccByIdNcc";
            return dt;
        }

        public DataTable GetListPhieuchiNoKhAll()
        {
            var result = from a in data.Phieuchis
                         where a.IdNoNCC > 0
                         select new
                         {
                             a.IdPhieuchi,
                             a.IdPhieunhap,
                             a.IdNhacungcap,
                             a.IdKho,
                             a.IdTiente,
                             a.Lydo,
                             a.MaPhieuchi,
                             a.Ngaychi,
                             a.Nguoinhan,
                             a.Sotien,
                             a.SotienQuydoi,
                             a.Tygia,
                             a.IdNguoilap,
                             a.Donvi,
                             a.Diachi,
                             a.IdNoNcc
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "GetListPhieuchiNoKhAll";
            return dt;
        }

        public DataTable LoadChitietNoByIdNcc(long idNhacungcap)
        {

            var result = from a in data.NoNCCs
                         join b in data.Phieunhaps on a.IdPhieunhap equals b.IdPhieunhap
                         where a.IdNhacungcap == idNhacungcap
                         orderby a.Ngay descending
                         select new
                         {
                             a.IdPhieunhap,
                             b.MaPhieunhap,
                             a.IdNhacungcap,
                             a.Ngay,
                             a.Psno,
                             a.SotienHoadon
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "LoadChitietNoByIdNcc";
            return dt;
        }

        public Phieuchi LoadPhieuchiByIdPhieunhap(long idPhieunhap)
        {

            var result = from a in data.Phieuchis
                         where a.IdPhieunhap == idPhieunhap && a.IdNoNcc == 0
                         select a;

            return result.ToList().Count > 0 ? result.First() : new Phieuchi();
        }

        // xoa phieu chi theo idPhieunhap
        public void DeleteNoNccByIdPhieunhap(long idPhieunhap)
        {

            var result = from a in data.NoNCCs
                         where a.IdPhieunhap == idPhieunhap
                         select a;

            foreach (var entity in result.ToList())
            {
                data.DeleteObject(entity);
                data.SaveChanges();
            }
        }

        #endregion

        #region tra hang hoa Sales_Khachtra_Hanghoa

        public bool AddKhachtra(Khachtra_Hanghoa entity)
        {
            data.AddToKhachtra_Hanghoa(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateKhachtra(Khachtra_Hanghoa entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelKhachtra(Khachtra_Hanghoa entity)
        {
            //DelChitiettrahangAllbyID(entity.IdTrahang);
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool AddChitiettrahang(Chitiet_Trahang entity)
        {
            data.AddToChitiet_Trahang(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateChitiettrahang(Chitiet_Trahang entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitiettrahang(Chitiet_Trahang entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitiettrahangAllbyID(long idTrahang)
        {

            var result = from a in data.Chitiet_Trahang
                         where a.IdTrahang == idTrahang
                         select a;

            foreach (var entity in result.ToList())
            {
                data.DeleteObject(entity);
                data.SaveChanges();
            }
            return true;
        }

        public DataTable loadDSKhachtraAll()
        {

            var result = from a in data.Khachtra_Hanghoa
                         select new
                         {
                             a.IdHoadon,
                             a.Tongtien,
                             a.IdKhachhang,
                             a.IdKho,
                             a.IdNguoilap,
                             a.Ngaytra,
                             a.IdTrahang,
                             a.Ghichu

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSKhachtraAll";
            return dt;
        }

        public DataTable loadDSKhachtraByDate(DateTime ngaybd, DateTime ngaykt)
        {

            var result = from a in data.Khachtra_Hanghoa
                         join b in data.Hoadons on a.IdHoadon equals b.IdHoadon
                         //join c in data.SalesKhachtraHanghoa on a.IdTrahang equals c.IdTrahang
                         where a.Ngaytra >= ngaybd && a.Ngaytra < ngaykt
                         select new
                         {
                             a.IdHoadon,
                             b.MaHoadon,
                             b.Ngay,
                             a.Tongtien,
                             a.IdKhachhang,
                             a.IdKho,
                             a.IdNguoilap,
                             a.Ngaytra,
                             a.IdTrahang,
                             a.MaTrahang,
                             a.Ghichu

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSKhachtraAll";
            return dt;
        }

        public DataTable loadDSTrahangById(long idTrahang)
        {

            var result = from a in data.Chitiet_Trahang
                         where a.IdTrahang == idTrahang
                         select new
                         {
                             a.IdTrahang,
                             Soluongtra = a.Soluong,
                             a.Dongia,
                             a.IdDonvitinh,
                             a.IdHanghoa,
                             a.IdChitiet

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSTrahangById";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluongtra"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            return dt;
        }


        #endregion

        #region tra hang hoa nha cung cap

        public bool AddTrahangNcc(Trahang_NCC entity)
        {
            data.AddToTrahang_NCC(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateTrahangNcc(Trahang_NCC entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelTrahangNcc(Trahang_NCC entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool AddChitietTraNcc(Chitiet_TraNCC entity)
        {
            data.AddToChitiet_TraNCC(entity);
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateChitietTraNcc(Chitiet_TraNCC entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitietTraNcc(Chitiet_TraNCC entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitietTraNccAllbyID(long idTrahang)
        {

            var result = from a in data.Chitiet_TraNCC
                         where a.IdTrahang == idTrahang
                         select a;
            foreach (var entity in result.ToList())
            {
               data.DeleteObject(entity);
            }

            return true;
        }

        public DataTable loadDSTrahangNccAll()
        {
            var result = from a in data.Trahang_NCC
                         select new
                         {
                             a.MaPhieutra,
                             a.Tongtien,
                             a.IdNhacungcap,
                             a.IdKho,
                             a.Ngaytra,
                             a.IdTrahang,
                             a.Ghichu,
                             a.IdNguoilap

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSTrahangNccAll";
            return dt;
        }

        public DataTable loadDSTrahangNccByDate(DateTime ngaybd, DateTime ngaykt)
        {

            var result = from a in data.Trahang_NCC
                         where a.Ngaytra >= ngaybd && a.Ngaytra <= ngaykt
                         select new
                         {
                             a.MaPhieutra,
                             a.Tongtien,
                             a.IdNhacungcap,
                             a.IdKho,
                             a.Ngaytra,
                             a.IdTrahang,
                             a.Ghichu,
                             a.IdNguoilap
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSTrahangNccByDate";
            return dt;
        }

        public DataTable loadDSChitietTraNccById(long idTrahang)
        {
            var result = from a in data.Chitiet_TraNCC
                         where a.IdTrahang == idTrahang
                         select new
                         {
                             a.IdTrahang,
                             a.Soluong,
                             a.Dongia,
                             a.IdHanghoa,
                             a.IdChitiet,
                             a.IdDonvitinh

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSChitietTraNccById";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            else
            {
                dt.Columns.Add("IdTrahang", typeof(long));
                dt.Columns.Add("IdDonvitinh", typeof(long));
                dt.Columns.Add("IdChitiet", typeof(long));
                dt.Columns.Add("Dongia", typeof(decimal));
                dt.Columns.Add("Soluong", typeof(long));
                dt.Columns.Add("IdHanghoa", typeof(long));
                dt.Columns.Add("Thanhtien", typeof(decimal));
            }
            return dt;
        }

        #endregion

        #region Nhập kho

        public bool AddNhapkho(SalesNhapkhoEntity entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateNhapkho(SalesNhapkhoEntity entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelNhapkho(SalesNhapkhoEntity entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool AddChitietNhapkho(SalesChitietNhapkhoEntity entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateChitietNhapkho(SalesChitietNhapkhoEntity entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitietNhapkho(SalesChitietNhapkhoEntity entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitietNhapkhoAllbyID(long idNhapkho)
        {

            var result = from a in data.SalesChitietNhapkho
                         where a.IdNhapkho == idNhapkho
                         select a;

            foreach (var entity in result.ToList())
            {
                entity.Delete();
            }
            return true;
        }

        public DataTable loadDSNhapkhoAll()
        {

            var result = from a in data.SalesNhapkho
                         select new
                         {
                             a.MaNhapkho,
                             a.IdKho,
                             a.Ngaynhap,
                             a.IdNhapkho,
                             a.Ghichu,
                             a.IdNguoilap

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSNhapkhoAll";
            return dt;
        }

        public DataTable loadDSNhapkhoByDate(DateTime ngaybd, DateTime ngaykt)
        {

            var result = from a in data.SalesNhapkho
                         where a.Ngaynhap >= ngaybd && a.Ngaynhap < ngaykt
                         select new
                         {
                             a.MaNhapkho,
                             a.IdKho,
                             a.Ngaynhap,
                             a.IdNhapkho,
                             a.Ghichu,
                             a.IdNguoilap
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSNhapkhoByDate";
            return dt;
        }

        public DataTable loadDSChitietNhapkhoById(long idNhapkho)
        {

            var result = from a in data.SalesChitietNhapkho
                         where a.IdNhapkho == idNhapkho
                         select new
                         {
                             a.IdNhapkho,
                             a.Soluong,
                             a.Dongia,
                             a.IdHanghoa,
                             a.IdChitiet,
                             a.IdDonvitinh

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSChitietNhapkhoById";
            if (dt.Rows.Count > 0)
            {

            }
            else
            {
                dt.Columns.Add("IdNhapkho", typeof(long));
                dt.Columns.Add("IdDonvitinh", typeof(long));
                dt.Columns.Add("IdChitiet", typeof(long));
                dt.Columns.Add("Dongia", typeof(decimal));
                dt.Columns.Add("Soluong", typeof(long));
                dt.Columns.Add("IdHanghoa", typeof(long));

            }
            return dt;
        }

        #endregion

        #region xuat kho

        public bool AddXuatkho(SalesXuatkhoEntity entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateXuatkho(SalesXuatkhoEntity entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelXuatkho(SalesXuatkhoEntity entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool AddChitietXuatkho(SalesChitietXuatkhoEntity entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool UpdateChitietXuatkho(SalesChitietXuatkhoEntity entity)
        {
            return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitietXuatkho(SalesChitietXuatkhoEntity entity)
        {
            data.DeleteObject(entity); return data.SaveChanges() > 0 ? true : false;
        }

        public bool DelChitietXuatkhoAllbyID(long idXuatkho)
        {

            var result = from a in data.SalesChitietXuatkho
                         where a.IdXuatkho == idXuatkho
                         select a;

            foreach (var entity in result.ToList())
            {
                entity.Delete();
            }
            return true;
        }

        public DataTable loadDSXuatkhoAll()
        {

            var result = from a in data.SalesXuatkho
                         select new
                         {
                             a.MaXuatkho,
                             a.IdKho,
                             a.Ngayxuat,
                             a.IdXuatkho,
                             a.Ghichu,
                             a.IdNguoilap

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSXuatkhoAll";
            return dt;
        }

        public DataTable loadDSXuatkhoByDate(DateTime ngaybd, DateTime ngaykt)
        {

            var result = from a in data.SalesXuatkho
                         where a.Ngayxuat >= ngaybd && a.Ngayxuat < ngaykt
                         select new
                         {
                             a.MaXuatkho,
                             a.IdKho,
                             a.Ngayxuat,
                             a.IdXuatkho,
                             a.Ghichu,
                             a.IdNguoilap
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSXuatkhoByDate";
            return dt;
        }

        public DataTable loadDSChitietXuatkhoById(long idXuatkho)
        {

            var result = from a in data.SalesChitietXuatkho
                         where a.IdXuatkho == idXuatkho
                         select new
                         {
                             a.IdXuatkho,
                             a.Soluong,
                             a.Dongia,
                             a.IdHanghoa,
                             a.IdChitiet,
                             a.IdDonvitinh
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "loadDSChitietXuatkhoById";
            if (dt.Rows.Count > 0)
            {

            }
            else
            {
                dt.Columns.Add("IdXuatkho", typeof(long));
                dt.Columns.Add("IdDonvitinh", typeof(long));
                dt.Columns.Add("IdChitiet", typeof(long));
                dt.Columns.Add("Dongia", typeof(decimal));
                dt.Columns.Add("Soluong", typeof(long));
                dt.Columns.Add("IdHanghoa", typeof(long));

            }
            return dt;
        }


        #endregion

        #region phan report

        #region phan kho hàng

        public DataTable RptThekho(DateTime ngaybd, DateTime ngaykt, long idkho, long idHanghoa)
        {
            return _Sales.SalesThekho(ngaybd, ngaykt, idkho, idHanghoa);
            //return RetrievalProcedures.SalesThekho(ngaybd,ngaykt,idkho,idHanghoa);
        }

        public DataTable RptNhapxuatton(DateTime ngaybd, DateTime ngaykt, long idkho, long idNhomHanghoa, long idLoaiHanghoa, long idHanghoa)
        {
            return _Sales.RptNhapxuatton(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
            //return RetrievalProcedures.SalesNhapxuatton(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
        }

        public DataTable RptChitietHangnhap(DateTime ngaybd, DateTime ngaykt, long idkho, long idNhomHanghoa, long idLoaiHanghoa, long idHanghoa)
        {
            return _Sales.RptChitietHangnhap(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
            //return RetrievalProcedures.RptChitietHangnhap(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
        }

        public DataTable RptChitietHangxuat(DateTime ngaybd, DateTime ngaykt, long idkho, long idNhomHanghoa, long idLoaiHanghoa, long idHanghoa)
        {
            return _Sales.RptChitietHangxuat(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
            //return RetrievalProcedures.RptChitietHangxuat(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
        }

        public DataTable RptTonghopHangnhap(DateTime ngaybd, DateTime ngaykt, long idkho, long idNhomHanghoa, long idLoaiHanghoa, long idHanghoa)
        {
            return _Sales.RptTonghopHangnhap(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
            //return RetrievalProcedures.RptTonghopHangnhap(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
        }

        public DataTable RptTonghopHangxuat(DateTime ngaybd, DateTime ngaykt, long idkho, long idNhomHanghoa, long idLoaiHanghoa, long idHanghoa)
        {
            return _Sales.RptTonghopHangxuat(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
            //return RetrievalProcedures.RptTonghopHangxuat(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);
        }

        public DataTable RptTrahangNcc(DateTime ngaybd, DateTime ngaykt, long idkho, long idNhomHanghoa, long idLoaiHanghoa, long idHanghoa)
        {
            //return RetrievalProcedures.RptTrahangNcc(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);

            var result = from th in data.SalesTrahangNcc
                         join ctth in data.SalesChitietTraNcc on th.IdTrahang equals ctth.IdTrahang
                         join hh in data.SalesDmHanghoa on ctth.IdHanghoa equals hh.IdHanghoa
                         join loaihh in data.SalesDmLoaiHanghoa on hh.IdLoaiHanghoa equals loaihh.IdLoaiHanghoa
                         join dvt in data.SalesDmDonvitinh on ctth.IdDonvitinh equals dvt.IdDonvitinh into dvt1
                         from dvt2 in dvt1.DefaultIfEmpty()

                         where (th.Ngaytra >= ngaybd && th.Ngaytra <= ngaykt)
                                  && (idNhomHanghoa == 0 || loaihh.IdNhomHanghoa == idNhomHanghoa)
                                  && (idLoaiHanghoa == 0 || loaihh.IdLoaiHanghoa == idLoaiHanghoa)
                                  && (idHanghoa == 0 || hh.IdHanghoa == idHanghoa)

                         group ctth by new
                         {
                             hh.MaHanghoa,
                             hh.TenHanghoa,
                             loaihh.IdLoaiHanghoa,
                             loaihh.TenLoaiHanghoa,
                             ctth.Dongia,
                             ctth.IdDonvitinh,
                             dvt2.TenDonvitinh
                         } into h
                         select new
                         {

                             h.Key.MaHanghoa,
                             h.Key.TenHanghoa,
                             h.Key.IdLoaiHanghoa,
                             h.Key.TenLoaiHanghoa,
                             h.Key.IdDonvitinh,
                             h.Key.TenDonvitinh,
                             h.Key.Dongia,
                             SoLuong = h.Sum(groupItem => groupItem.Soluong)
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "RptTrahangNcc";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            return dt;

        }

        public DataTable RptKhachtraHanghoa(DateTime ngaybd, DateTime ngaykt, long idkho, long idNhomHanghoa, long idLoaiHanghoa, long idHanghoa)
        {
            //return RetrievalProcedures.RptKhachtraHanghoa(ngaybd, ngaykt, idkho, idNhomHanghoa, idLoaiHanghoa, idHanghoa);

            var result = from kt in data.SalesKhachtraHanghoa
                         join ctkt in data.SalesChitietTrahang on kt.IdTrahang equals ctkt.IdTrahang
                         join hh in data.SalesDmHanghoa on ctkt.IdHanghoa equals hh.IdHanghoa
                         join loaihh in data.SalesDmLoaiHanghoa on hh.IdLoaiHanghoa equals loaihh.IdLoaiHanghoa
                         join dvt in data.SalesDmDonvitinh on ctkt.IdDonvitinh equals dvt.IdDonvitinh into dvt1
                         from dvt2 in dvt1.DefaultIfEmpty()

                         where (kt.Ngaytra >= ngaybd && kt.Ngaytra <= ngaykt)
                                  && (idNhomHanghoa == 0 || loaihh.IdNhomHanghoa == idNhomHanghoa)
                                  && (idLoaiHanghoa == 0 || loaihh.IdLoaiHanghoa == idLoaiHanghoa)
                                  && (idHanghoa == 0 || hh.IdHanghoa == idHanghoa)

                         group ctkt by new
                         {
                             hh.MaHanghoa,
                             hh.TenHanghoa,
                             loaihh.IdLoaiHanghoa,
                             loaihh.TenLoaiHanghoa,
                             ctkt.Dongia,
                             ctkt.IdDonvitinh,
                             dvt2.TenDonvitinh
                         } into h
                         select new
                         {

                             h.Key.MaHanghoa,
                             h.Key.TenHanghoa,
                             h.Key.IdLoaiHanghoa,
                             h.Key.TenLoaiHanghoa,
                             h.Key.IdDonvitinh,
                             h.Key.TenDonvitinh,
                             h.Key.Dongia,
                             SoLuong = h.Sum(groupItem => groupItem.Soluong)
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "RptKhachtraHanghoa";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            return dt;
        }

        #endregion

        // 3/4
        #region phan ban hàng

        //ok - da chuyen
        public DataTable RptGetChitiettienbanhang(DateTime ngaybd, DateTime ngaykt, long idkho, long idkhachhang)
        {
            //return RetrievalProcedures.GetChitiettienbanhang(ngaybd, ngaykt, idkho, idkhachhang);

            var result = from hd in data.SalesHoadon
                         join cthd in data.SalesChitietHoadon on hd.IdHoadon equals cthd.IdHoadon
                         join hh in data.SalesDmHanghoa on cthd.IdHanghoa equals hh.IdHanghoa
                         join loaihh in data.SalesDmLoaiHanghoa on hh.IdLoaiHanghoa equals loaihh.IdLoaiHanghoa
                         join dvt in data.SalesDmDonvitinh on cthd.IdDonvitinh equals dvt.IdDonvitinh into dvt1
                         from dvt2 in dvt1.DefaultIfEmpty()
                         join kh in data.SalesDmKhachhang on hd.IdKhachhang equals kh.IdKhachhang into kh1
                         from kh2 in kh1.DefaultIfEmpty()
                         where (hd.Ngay >= ngaybd && hd.Ngay <= ngaykt)
                                && (idkhachhang == 0 || hd.IdKhachhang == idkhachhang)
                                && hd.IdKho == idkho

                         group cthd by new
                         {
                             hd.MaHoadon,
                             hd.Ngay,
                             hd.IdKhachhang,
                             kh2.MaKhachhang,
                             kh2.TenKhachhang,
                             hh.MaHanghoa,
                             hh.TenHanghoa,
                             loaihh.IdLoaiHanghoa,
                             loaihh.TenLoaiHanghoa,
                             cthd.Dongia,
                             cthd.IdDonvitinh,
                             dvt2.TenDonvitinh
                         } into h
                         select new
                         {
                             Ngaychungtu = h.Key.Ngay,
                             Sochungtu = h.Key.MaHoadon,
                             h.Key.MaHanghoa,
                             h.Key.TenHanghoa,
                             h.Key.IdKhachhang,
                             h.Key.MaKhachhang,
                             h.Key.TenKhachhang,
                             h.Key.IdLoaiHanghoa,
                             h.Key.TenLoaiHanghoa,
                             h.Key.IdDonvitinh,
                             h.Key.TenDonvitinh,
                             h.Key.Dongia,
                             SoLuong = h.Sum(groupItem => groupItem.Soluong)
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "RptGetChitiettienbanhang";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            return dt;
        }

        //ok - da chuyen
        public DataTable RptGetChitiettienhangkhachtra(DateTime ngaybd, DateTime ngaykt, long idkho, long idkhachhang)
        {
            //return RetrievalProcedures.GetChitiettienhangkhachtra(ngaybd, ngaykt, idkho, idkhachhang);

            var result = from kt in data.SalesKhachtraHanghoa
                         join ctkt in data.SalesChitietTrahang on kt.IdTrahang equals ctkt.IdTrahang
                         join hh in data.SalesDmHanghoa on ctkt.IdHanghoa equals hh.IdHanghoa
                         join loaihh in data.SalesDmLoaiHanghoa on hh.IdLoaiHanghoa equals loaihh.IdLoaiHanghoa
                         join dvt in data.SalesDmDonvitinh on ctkt.IdDonvitinh equals dvt.IdDonvitinh into dvt1
                         from dvt2 in dvt1.DefaultIfEmpty()
                         join kh in data.SalesDmKhachhang on kt.IdKhachhang equals kh.IdKhachhang into kh1
                         from kh2 in kh1.DefaultIfEmpty()
                         where (kt.Ngaytra >= ngaybd && kt.Ngaytra <= ngaykt)
                                && (idkhachhang == 0 || kt.IdKhachhang == idkhachhang)
                                && kt.IdKho == idkho

                         group ctkt by new
                         {
                             kt.MaTrahang,
                             kt.Ngaytra,
                             kt.IdKhachhang,
                             kh2.MaKhachhang,
                             kh2.TenKhachhang,
                             hh.MaHanghoa,
                             hh.TenHanghoa,
                             loaihh.IdLoaiHanghoa,
                             loaihh.TenLoaiHanghoa,
                             ctkt.Dongia,
                             ctkt.IdDonvitinh,
                             dvt2.TenDonvitinh
                         } into h
                         select new
                         {
                             Ngaychungtu = h.Key.Ngaytra,
                             Sochungtu = h.Key.MaTrahang,
                             h.Key.MaHanghoa,
                             h.Key.TenHanghoa,
                             h.Key.IdKhachhang,
                             h.Key.MaKhachhang,
                             h.Key.TenKhachhang,
                             h.Key.IdLoaiHanghoa,
                             h.Key.TenLoaiHanghoa,
                             h.Key.IdDonvitinh,
                             h.Key.TenDonvitinh,
                             h.Key.Dongia,
                             SoLuong = h.Sum(groupItem => groupItem.Soluong)
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "RptGetChitiettienhangkhachtra";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            return dt;

        }

        //ok - da chuyen
        public DataTable RptGetChitietThuCongno(DateTime ngaybd, DateTime ngaykt, long idkhachhang)
        {
            //return RetrievalProcedures.GetChitietThuCongno(ngaybd, ngaykt, idkhachhang);

            var result = from pt in data.Phieuthus
                         join hd in data.SalesHoadon on pt.IdHoadon equals hd.IdHoadon
                         join kh in data.SalesDmKhachhang on pt.IdKhachhang equals kh.IdKhachhang

                         where pt.Ngaythu >= ngaybd && pt.Ngaythu <= ngaykt
                              && (idkhachhang == 0 || pt.IdKhachhang == idkhachhang)

                         select new
                         {
                             Ngaychungtu = pt.Ngaythu,
                             Sochungtu = pt.MaPhieuthu,
                             pt.IdKhachhang,
                             kh.TenKhachhang,
                             pt.Lydo,
                             pt.Sotien,
                             Chungtugoc = hd.MaHoadon

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "RptGetChitietThuCongno";
            return dt;
        }

        public DataTable RptGetChungtuCongnoChuathu(long idkhachhang)
        {
            return _Sales.RptGetChungtuCongnoChuathu(idkhachhang);
            //return RetrievalProcedures.GetChungtuCongnoChuathu(idkhachhang);
        }

        #endregion

        // 3/4
        #region phan mua hàng

        //ok - da chuyen
        public DataTable RptGetChitiettienmuahang(DateTime ngaybd, DateTime ngaykt, long idkho, long idNhacungcap)
        {
            //return RetrievalProcedures.GetChitiettienmuahang(ngaybd, ngaykt, idkho, idNhacungcap);

            var result = from pn in data.Phieunhaps
                         join ctpn in data.SalesChitietPhieunhap on pn.IdPhieunhap equals ctpn.IdPhieunhap
                         join hh in data.SalesDmHanghoa on ctpn.IdHanghoa equals hh.IdHanghoa
                         join loaihh in data.SalesDmLoaiHanghoa on hh.IdLoaiHanghoa equals loaihh.IdLoaiHanghoa
                         join dvt in data.SalesDmDonvitinh on ctpn.IdDonvitinh equals dvt.IdDonvitinh into dvt1
                         from dvt2 in dvt1.DefaultIfEmpty()
                         join ncc in data.SalesDmNhacungcap on pn.IdNhacungcap equals ncc.IdNhacungcap into ncc1
                         from ncc2 in ncc1.DefaultIfEmpty()
                         where (pn.Ngay >= ngaybd && pn.Ngay <= ngaykt)
                                && (idNhacungcap == 0 || pn.IdNhacungcap == idNhacungcap)
                                && pn.IdKho == idkho

                         group ctpn by new
                         {
                             pn.MaPhieunhap,
                             pn.NgayNhap,
                             pn.IdNhacungcap,
                             ncc2.MaNhacungcap,
                             ncc2.TenNhacungcap,
                             hh.MaHanghoa,
                             hh.TenHanghoa,
                             loaihh.IdLoaiHanghoa,
                             loaihh.TenLoaiHanghoa,
                             ctpn.Dongia,
                             ctpn.IdDonvitinh,
                             dvt2.TenDonvitinh
                         } into h
                         select new
                             {
                                 Ngaychungtu = h.Key.NgayNhap,
                                 Sochungtu = h.Key.MaPhieunhap,
                                 h.Key.MaHanghoa,
                                 h.Key.TenHanghoa,
                                 h.Key.IdNhacungcap,
                                 h.Key.MaNhacungcap,
                                 h.Key.TenNhacungcap,
                                 h.Key.IdLoaiHanghoa,
                                 h.Key.TenLoaiHanghoa,
                                 h.Key.IdDonvitinh,
                                 h.Key.TenDonvitinh,
                                 h.Key.Dongia,
                                 SoLuong = h.Sum(groupItem => groupItem.Soluong)
                             };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "RptGetChitiettienmuahang";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            return dt;
        }

        //ok - da chuyen
        public DataTable RptGetChitiettienhangTraNcc(DateTime ngaybd, DateTime ngaykt, long idkho, long idNhacungcap)
        {
            //return RetrievalProcedures.GetChitiettienhangTraNcc(ngaybd, ngaykt, idkho, idNhacungcap);

            var result = from th in data.SalesTrahangNcc
                         join ctth in data.SalesChitietTraNcc on th.IdTrahang equals ctth.IdTrahang
                         join hh in data.SalesDmHanghoa on ctth.IdHanghoa equals hh.IdHanghoa
                         join loaihh in data.SalesDmLoaiHanghoa on hh.IdLoaiHanghoa equals loaihh.IdLoaiHanghoa
                         join dvt in data.SalesDmDonvitinh on ctth.IdDonvitinh equals dvt.IdDonvitinh into dvt1
                         from dvt2 in dvt1.DefaultIfEmpty()
                         join ncc in data.SalesDmNhacungcap on th.IdNhacungcap equals ncc.IdNhacungcap into ncc1
                         from ncc2 in ncc1.DefaultIfEmpty()
                         where (th.Ngaytra >= ngaybd && th.Ngaytra <= ngaykt)
                                && (idNhacungcap == 0 || th.IdNhacungcap == idNhacungcap)
                                && th.IdKho == idkho

                         group ctth by new
                         {
                             th.MaPhieutra,
                             th.Ngaytra,
                             th.IdNhacungcap,
                             ncc2.MaNhacungcap,
                             ncc2.TenNhacungcap,
                             hh.MaHanghoa,
                             hh.TenHanghoa,
                             loaihh.IdLoaiHanghoa,
                             loaihh.TenLoaiHanghoa,
                             ctth.Dongia,
                             ctth.IdDonvitinh,
                             dvt2.TenDonvitinh
                         } into h
                         select new
                         {
                             Ngaychungtu = h.Key.Ngaytra,
                             Sochungtu = h.Key.MaPhieutra,
                             h.Key.MaHanghoa,
                             h.Key.TenHanghoa,
                             h.Key.IdNhacungcap,
                             h.Key.MaNhacungcap,
                             h.Key.TenNhacungcap,
                             h.Key.IdLoaiHanghoa,
                             h.Key.TenLoaiHanghoa,
                             h.Key.IdDonvitinh,
                             h.Key.TenDonvitinh,
                             h.Key.Dongia,
                             SoLuong = h.Sum(groupItem => groupItem.Soluong)
                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "RptGetChitiettienhangTraNcc";
            if (dt.Rows.Count > 0)
            {
                dt.Columns.Add("Thanhtien", typeof(decimal));
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dt.Rows[i]["Thanhtien"] = decimal.Parse(dt.Rows[i]["Soluong"].ToString()) * decimal.Parse(dt.Rows[i]["Dongia"].ToString());
                }
            }
            return dt;
        }

        //ok - da chuyen
        public DataTable RptGetChitietChiCongno(DateTime ngaybd, DateTime ngaykt, long idNhacungcap)
        {
            //return RetrievalProcedures.GetChitietChiCongno(ngaybd, ngaykt, idNhacungcap);

            var result = from pc in data.Phieuchis
                         join pn in data.Phieunhaps on pc.IdPhieunhap equals pn.IdPhieunhap
                         join ncc in data.SalesDmNhacungcap on pc.IdNhacungcap equals ncc.IdNhacungcap

                         where pc.Ngaychi >= ngaybd && pc.Ngaychi <= ngaykt
                              && (idNhacungcap == 0 || pc.IdNhacungcap == idNhacungcap)

                         select new
                         {
                             Ngaychungtu = pc.Ngaychi,
                             Sochungtu = pc.MaPhieuchi,
                             pc.IdNhacungcap,
                             ncc.TenNhacungcap,
                             pc.Lydo,
                             pc.Sotien,
                             Chungtugoc = pn.MaPhieunhap

                         };

            var dt = commonclass.LINQToDataTable(result);
            dt.TableName = "RptGetChitietChiCongno";
            return dt;
        }

        public DataTable RptGetChungtuCongnoChuatra(long idNhacungcap)
        {
            return _Sales.RptGetChungtuCongnoChuatra(idNhacungcap);
            //return RetrievalProcedures.GetChungtuCongnoChuatra(idNhacungcap);
        }

        #endregion

        #endregion


    }
}
